package com.gs.sqlite;

import org.springframework.beans.factory.annotation.Value;

import java.io.*;
import java.sql.*;
import java.sql.Date;
import java.text.MessageFormat;
import java.util.*;

public class DB {
    //数据库驱动，默认为Oracle
    public static String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";

    //数据库连接地址
    public static String DB_URL = "";

    public static String USER = "";

    public static String PASS = "";

    Connection CONN = null;

    //PreparedStatement PSTM = null;

    public DB(String url, String user, String pass) {
        DB_URL = url;
        USER = user;
        PASS = pass;
    }

    public DB() {
        /*Properties props = new Properties();
        try {
            String configFilePath = System.getProperty("antgis.web.oa.root")+"/WEB-INF/config.properties";
            props.load(new FileInputStream(configFilePath));
        }catch (FileNotFoundException e){

        }catch (IOException e){

        }*/


//        JDBC_DRIVER = Config.DB_DRIVER;
//        DB_URL = Config.DB_URL;
//        USER = Config.DB_USER;
//        PASS = Config.DB_PWD;
    }

    public Connection getConnection() {
        try {
            Class.forName(JDBC_DRIVER);
            CONN = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (ClassNotFoundException e) {

        } catch (SQLException e) {

        }
        return CONN;
    }

    public ResultSet getResultSet(String sql) {
        ResultSet resultSet = null;
        //PreparedStatement pstm = null;
        try {
            CONN = getConnection();
            PreparedStatement PSTM = CONN.prepareStatement(sql);
            resultSet = PSTM.executeQuery();
            return resultSet;
        } catch (SQLException e) {
            return resultSet;
        } finally {

        }
    }

    public void close() {
        /*if(PSTM !=null) {
            try {
                PSTM.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }*/

        try {
            if (CONN != null && (!CONN.isClosed())) {
                try {
                    CONN.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<Map<String, Object>> getList(String sql) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        ResultSet rs = getResultSet(sql);
        list = convertToList(rs);
        return list;
    }

    public List<Map<String, Object>> convertToList(ResultSet rs) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
            int columnCount = md.getColumnCount();   //获得列数
            while (rs.next()) {
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {

        }

        return list;
    }

    public ResultSet getResultSet(String tableName, String filter) {
        String sql = MessageFormat.format("select * from {0} where {1}", tableName, filter);
        return getResultSet(sql);
    }

    public ResultSet getResultSet(String tableName, String columnList, String filter) {
        String sql = MessageFormat.format("select {2} from {0} where {1}", tableName, filter, columnList);
        return getResultSet(sql);
    }

    public ResultSet getResultSet(String tableName, String columnList, String filter, String orderColumnList) {
        String sql = MessageFormat.format("select {2} from {0} where {1} order by {3}", tableName, filter, columnList, orderColumnList);
        return getResultSet(sql);
    }

    public Object getFirstColumn(String sql) {
        ResultSet rs = getResultSet(sql);
        try {
            if (rs.next()) {
                return rs.getObject(0);
            }
        } catch (SQLException e) {

        }
        return null;
    }

    public String getSqlForPageSize(String tableName, Integer pageSize, Integer pageIndex, String where, String order) {
        return getSqlForPageSize(tableName, "*", pageSize, pageIndex, where, order);
    }

    public String getSqlForPageSize(String tableName, String sColumnList, int pageSize, int pageIndex, String where, String order) {
        String sSql = "";
        StringBuilder sb = new StringBuilder();
        sb.append("select " + sColumnList + " from (select t.*,rownum rno ");
        sb.append(" from (select * from " + tableName + " ");
        sb.append(" where " + where);
        sb.append(order);
        sb.append(") t where rownum <= ");
        sb.append((pageIndex + 1) * pageSize);
        sb.append(" )");
        sb.append(" where rno >= ");
        sb.append(pageIndex * pageSize + 1);
        sSql = sb.toString();
        return sSql;
    }

    public boolean judgeRecordExist(String tableName, String filter) {
        ResultSet rs = getResultSet(tableName, filter);
        try {
            if (rs.next())
                return true;
            else
                return false;
        } catch (SQLException e) {
            return false;
        }
    }

    public String formatTableName(String tableName) {
        return tableName.replace("\"", "");
    }

    public boolean judgeTableOrViewExist(String tableName) {
        String sql = "select count(*) from user_objects where object_type in ('TABLE','VIEW') AND upper(OBJECT_NAME)=upper('" + formatTableName(tableName) + "')";
        ResultSet rs = getResultSet(sql);
        return judgeCountValue(rs);
    }

    public boolean judgeColumnExist(String tableName, String columnName) {
        String sql = "select count(*) from user_tab_cols where upper(table_name)=upper('" + formatTableName(tableName) + "') and upper(COLUMN_NAME)=upper('" + columnName + "') order by COLUMN_ID";
        ResultSet rs = getResultSet(sql);
        return judgeCountValue(rs);
    }

    private boolean judgeCountValue(ResultSet rs) {
        try {
            if (rs.next()) {
                if (Integer.parseInt(rs.getObject(0).toString()) == 1)
                    return true;
                else
                    return false;
            } else {
                return false;
            }
        } catch (SQLException e) {
            return false;
        }
    }

    public HashMap getTableColumnType(String tableName) {
        String sql = "select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID from user_tab_cols where upper(table_name)=upper('" + formatTableName(tableName) + "') order by COLUMN_ID";
        ResultSet rs = getResultSet(sql);
        HashMap hm = new HashMap();
        try {
            while (rs.next()) {
                String name = rs.getString("COLUMN_NAME");
                String value = rs.getString("DATA_TYPE");
                hm.put(name, value);
            }
        } catch (SQLException e) {

        }

        return hm;
    }

    public boolean add(String tableName, LinkedHashMap<String, Object> hm) throws Exception {
        if (tableName == null || tableName.equals("")) {
            throw new Exception("表名不能为空字符串！");
        }

        String sColumns = "";
        String sValues = "";

        Iterator iter = hm.entrySet().iterator();
        while (iter.hasNext()) {
            Map.Entry entry = (Map.Entry) iter.next();
            sColumns += entry.getKey().toString() + ",";
            sValues += "?,";
        }
        /*for(String key:hm.keySet()){
            sColumns += key +",";
            sValues += "?,";
        }*/

        sColumns = sColumns.substring(0, sColumns.length() - 1);
        sValues = sValues.substring(0, sValues.length() - 1);
        String sql = MessageFormat.format("insert into {0} ({1}) values({2})", tableName, sColumns, sValues);

        return executeSql(sql, tableName, hm);
    }

    public boolean update(String tableName, LinkedHashMap<String, Object> hm, String filterColumns) throws Exception {
        if (tableName == null || tableName.equals("")) {
            throw new Exception("表名不能为空字符串！");
        }

        String filter = getFilterString(tableName, hm, filterColumns);

        return update2(tableName, hm, filter);
    }

    public boolean update2(String tableName, LinkedHashMap<String, Object> hm, String filter) throws Exception {
        if (tableName == null || tableName.equals("")) {
            throw new Exception("表名不能为空字符串！");
        }

        String str = "";
        Iterator iter = hm.entrySet().iterator();
        while (iter.hasNext()) {
            Map.Entry entry = (Map.Entry) iter.next();
            str += entry.getKey().toString() + "=?,";
        }

        str = str.substring(0, str.length() - 1);
        String sql = MessageFormat.format("update {0} set {1} where {2}", tableName, str, filter);
        return executeSql(sql, tableName, hm);
    }

    private String getFilterString(String tableName, LinkedHashMap<String, Object> hm, String filterColumns) {
        String[] arr = filterColumns.split(",");
        int length = arr.length;
        HashMap columnsType = getTableColumnType(tableName);
        String filter = "";
        for (int ii = 0; ii < length; ii++) {
            String columnType = columnsType.get(arr[ii]).toString();
            switch (columnType) {
                case "NUMBER":
                    filter += "and " + arr[ii] + "=" + hm.get(arr[ii]) + " ";
                    break;
                case "DATE":
                    filter += "and " + arr[ii] + "=to_date('" + hm.get(arr[ii]).toString() + "','yyyy-mm-dd hh24:mi:ss') ";
                    break;
                default:
                    filter += "and " + arr[ii] + "='" + hm.get(arr[ii]).toString() + "' ";
                    break;

            }
        }
        filter = filter.substring(3);
        return filter;
    }


    public boolean executeSql(String sql, String tableName, LinkedHashMap<String, Object> hm) {
        boolean bl = false;
        int i = 0;
        try {
            CONN = getConnection();
            PreparedStatement pstm = CONN.prepareStatement(sql);
            HashMap columnsType = getTableColumnType(tableName);

            Iterator iter = hm.entrySet().iterator();
            int index = 0;
            while (iter.hasNext()) {
                index += 1;
                Map.Entry entry = (Map.Entry) iter.next();
                String key = entry.getKey().toString();
                Object val = entry.getValue();
                String columnType = columnsType.get(key).toString();
                switch (columnType) {
                    case "NVARCHAR2":
                    case "NCHAR":
                    case "VARCHAR2":
                    case "CHAR":
                    case "NUMBER":
                        pstm.setString(index, val.toString());
                        break;
                    case "DATE":
                        java.sql.Timestamp dateTime = java.sql.Timestamp.valueOf(val.toString());
                        pstm.setTimestamp(index, dateTime);
                        break;
                    /*case "BLOB":
                        PSTM.setBlob(index,oracle.sql.BLOB.getEmptyBLOB());

                        break;*/
                    case "CLOB":
                        String str = val.toString();
                        Reader clobReader = new StringReader(str); // 将 text转成流形式
                        pstm.setClob(index, clobReader, str.length());
                        break;
                    default:
                        pstm.setString(index, val.toString());
                        break;

                }
            }

            i = pstm.executeUpdate();
            if (i > 0)
                bl = true;


        } catch (SQLException e) {
            String msg = e.getMessage();
        }


        return bl;
    }
}
